﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
   public class WebInfomationPublishServiceImpl : BaseServiceImpl, IWebInfomationPublishService
    {
        public IList getlist(int pageno, int pagesize, int template)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            if (pageno == 1)
            {
                sb.Append("SELECT count(Wi_id) as total FROM joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template;");
                sb.Append("select top " + pagesize + " * from joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template  order by Wi_recommend desc,Wi_id desc;");
            }
            else
            {
                sb.Append("SELECT count(Wi_id) as total FROM joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template;");
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by Wi_recommend desc,Wi_id desc) as rno from  joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template");
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by Wi_recommend desc,Wi_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            query.addParas("@template", template);
            return base.getList(query);
        }

        public IList getlistweb(int pageno, int pagesize, int template)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            if (pageno == 1)
            {
                sb.Append("SELECT count(Wi_id) as total FROM joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime);");
                sb.Append("select top " + pagesize + " * from joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime)  order by Wi_recommend desc,Wi_id desc;");
            }
            else
            {
                sb.Append("SELECT count(Wi_id) as total FROM joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime);");
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by Wi_recommend desc,Wi_id desc) as rno from  joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime)  ");
                sb.Append(") as a where a.rno>@start and a.rno<@end order by Wi_recommend desc,Wi_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            query.addParas("@template", template);
            return base.getList(query);
        }
        public IList getlistwebrule(int pageno, int pagesize, int template,int jwclass)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            if (pageno == 1)
            {
                sb.Append("SELECT count(Wi_id) as total FROM joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime) and Wi_classRule like '%," + jwclass.ToString() + ",%';");
                sb.Append("select top " + pagesize + " * from joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime) and Wi_classRule like '%," + jwclass.ToString() + ",%'  order by Wi_recommend desc,Wi_id desc;");
            }
            else
            {
                sb.Append("SELECT count(Wi_id) as total FROM joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime) and Wi_classRule like '," + jwclass.ToString() + ",';");
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by Wi_recommend desc,Wi_id desc) as rno from  joy.WebInfomationPublish where Wi_state=1 and Wi_template=@template and (getdate()>Wi_startTime and getdate()<Wi_endTime) and Wi_classRule like '%," + jwclass.ToString() + ",%' ");
                sb.Append(") as a where a.rno>@start and a.rno<@end order by Wi_recommend desc,Wi_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            query.addParas("@template", template);
            return base.getList(query);
        }
        public IList getinfomationbyidrule(int id,int jwclass)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select * from joy.WebInfomationPublish where Wi_id=@id and Wi_state=1 and Wi_classRule like '%,"+jwclass.ToString()+",%'");
            query.addParas("@id", id);
            return base.getList(query);
        }
        public IList getinfomationbyid(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select * from joy.WebInfomationPublish where Wi_id=@id and Wi_state=1");
            query.addParas("@id", id);
            return base.getList(query);
        }

        public int delete(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.WebInfomationPublish set Wi_state=0 WHERE Wi_id =@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public IList getclasslist()
        { 
            MyQuery query = new MyQuery();
            query.setQueryString("select uic_id,uic_class from joy.UserClass where uic_state=1");
            return base.getList(query);
        }

        public IList getxw()
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            sb.Append("select top 2 *,'" + ((int)DAL.webEnums.template.新闻中心_行业动态).ToString() + "' pid from joy.WebInfomationPublish where Wi_state=1 and Wi_template=" + ((int)DAL.webEnums.template.新闻中心_行业动态).ToString() + " and (getdate()>Wi_startTime and getdate()<Wi_endTime) ");
            sb.Append(" union ");
            sb.Append("select top 1 *,'" + ((int)DAL.webEnums.template.新闻中心_学校公告).ToString() + "' pid from joy.WebInfomationPublish where Wi_state=1 and Wi_template=" + ((int)DAL.webEnums.template.新闻中心_学校公告).ToString() + " and (getdate()>Wi_startTime and getdate()<Wi_endTime) ");
            sb.Append(" union ");
            sb.Append("select top 1 *,'" + ((int)DAL.webEnums.template.新闻中心_学校活动).ToString() + "' pid from joy.WebInfomationPublish where Wi_state=1 and Wi_template=" + ((int)DAL.webEnums.template.新闻中心_学校活动).ToString() + " and (getdate()>Wi_startTime and getdate()<Wi_endTime) ");
            sb.Append(" union ");
            sb.Append("select top 1 *,'" + ((int)DAL.webEnums.template.新闻中心_学校新闻).ToString() + "' pid from joy.WebInfomationPublish where Wi_state=1 and Wi_template=" + ((int)DAL.webEnums.template.新闻中心_学校新闻).ToString() + " and (getdate()>Wi_startTime and getdate()<Wi_endTime) order by Wi_recommend desc,Wi_id desc ");
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
    }
}
